﻿//#define SqlLog

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using DoubleH.Utility;
using Table = FCNS.Data.Table;
using System.Windows ;
using DoubleH.Utility.Configuration;
using System.Diagnostics;

namespace DataExchange
{
    public partial class MainWindow : Window
    {
        /// <summary>
        /// 用于直接sql语句插入到远程服务器
        /// </summary>
        class _SqlClass
        {
            public string TableName { get; set; }
            public string OrderNOList { get; set; }
            public string GuidStringList { get; set; }
            public bool UploadSuccess { get; set; }
            public DataTable Table { get; set; }

            List<string> uploadData = new List<string>();
            public List<string> UploadData
            {
                get { return uploadData; }
                set { uploadData = value; }
            }
        }

        SQLiteConnection conSqlite;
        SQLiteCommand cmdSqlite;
        private void LoadLocalDbFile()
        {
            conSqlite = new SQLiteConnection();
            cmdSqlite = new SQLiteCommand();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = FCNS.Data.DbDefine.dbFile;
            conSqlite.ConnectionString = connstr.ToString();
            conSqlite.Open();
            cmdSqlite.Connection = conSqlite;
        }

        private void DisposeLocalDbFile()
        {
            cmdSqlite.Dispose();
            conSqlite.Close();
            conSqlite.Dispose();
        }
        /// <summary>
        /// 如果总部服务器连接成功就加载本地数据库文件
        /// </summary>
        /// <returns></returns>
        private bool CheckServerSuccess()
        {
            bool b = DoubleH.Utility.Net.NetUtility.TestConnection(dataConfig.DataAddress, dataConfig.Port, dataConfig.TimeOut);
            if (!b)
                AddLog(DateTime.Now + ":远程服务器连接失败");

            return b;
        }



        /// <summary>
        /// 备份数据到远程指定服务器，非服务端的数据库。
        /// 仅支持POS的数据
        /// 每次读取的数据等于‘数据交换的时间（分）*10’
        /// </summary>
        private void BackupRemote()
        {
            //if (!checkBoxBackup.IsChecked.Value)
            //    return;


        }

        /// <summary>
        /// 从服务器中下载数据更新到本地数据库文件。
        /// 仅在程序运行后运行一次即可。
        /// </summary>
        private void DownloadServerData()
        {
            if (dataConfig.DataType == FCNS.Data.DataType.SQLITE.ToString())//单机版就没必要下载数据了
                return;

            if (!CheckServerSuccess())
                return;

            List<string> sqlStrings = new List<string>();
            //CorS 仅客户
            sqlStrings.Add("delete from CorS  ");
            sqlStrings.AddRange(FormatTableToSql(Table.CorS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from CorS where Enable=0 and (Flag=0 or Flag=2)")));
            //仓库
            sqlStrings.Add("delete from StoreS  ");
            sqlStrings.AddRange(FormatTableToSql(Table.StoreS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from StoreS where Enable=0 ")));
            //ProductS 仅POS所属仓库的商品
            sqlStrings.Add("delete from ProductS  ");
            sqlStrings.AddRange(FormatTableToSql(Table.ProductS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from ProductS where Enable=0 and (Flag=0 or Flag=1)")));
            sqlStrings.Add("delete from ProductSInStoreS ");
            sqlStrings.AddRange(FormatTableToSql(Table.ProductSInStoreS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from ProductSInStoreS where StoreSId in (select StoreSId from PosS where PosNO like '" + DoubleHConfig.AppConfig.PosNO + "')")));
            //GroupS  仅客户和商品
            sqlStrings.Add("delete from GroupS ");
            sqlStrings.AddRange(FormatTableToSql(Table.GroupS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from GroupS where Enable=0 and  (Flag=0 or Flag=1)")));
            //SysConfig
            sqlStrings.Add("delete from SysConfig  ");
            sqlStrings.AddRange(FormatTableToSql(Table.SysConfig.tableName, FCNS.Data.SQLdata.GetDataTable("select * from SysConfig")));
            //UserS  仅POS操作员
            sqlStrings.Add("delete from UserS  ");
            sqlStrings.AddRange(FormatTableToSql(Table.UserS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from UserS where Enable=0 and  Flag=2 ")));
            //UniqueS  仅 计量单位 = 0, 支付方式5， 品牌11,产地12,会员分类16, 商品属性17,
            sqlStrings.Add("delete from UniqueS  ");
            sqlStrings.AddRange(FormatTableToSql(Table.UniqueS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from UniqueS where Enable=0 and (Flag=0 or Flag=5 or Flag=11 or Flag=12 or Flag=16 or Flag=17)")));
            //PosS
            sqlStrings.Add("delete from PosS  ");
            sqlStrings.AddRange(FormatTableToSql(Table.PosS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from PosS where PosNO like '" + DoubleHConfig.AppConfig.PosNO + "'")));
            ////请货单的上传和配送单的入账处理必须要联网实时传输，这里只下载要发送到门店的配送单
            //sqlStrings.Add("delete from PosRequestS  ");
            //sqlStrings.AddRange(FormatTableToSql(Table.PosRequestS.tableName, FCNS.Data.SQLdata.GetDataTable("select * from PosRequestS where Flag=1 and Enable=1 and  ShopFlag like (select ShopFlag from PosS where PosNO like '" + DoubleHConfig.AppConfig.PosNO + "')")));

            LoadLocalDbFile();
            cmdSqlite.Parameters.Add(cmdSqlite.CreateParameter());
            System.Data.Common.DbTransaction trans = conSqlite.BeginTransaction();

            try
            {
                for (int i = 0; i < sqlStrings.Count; i++)
                {
                    cmdSqlite.CommandText = sqlStrings[i];
                    cmdSqlite.Parameters[0].Value = i.ToString();
                    cmdSqlite.ExecuteNonQuery();
                }
                trans.Commit();
                AddLog(DateTime.Now + ":远程数据下载->成功");
            }
            catch (Exception ee)
            {
                AddLog(DateTime.Now + ":远程数据库下载失败->" + ee.Message);
                trans.Rollback();
            }
            finally
            {
                trans.Dispose();
                SavePosOffLineMode();//这个必须的
                DisposeLocalDbFile();
            }
        }

        /// <summary>
        /// 上传POS端的离线数据
        /// 按配置更新的时间，每分钟6张单计算。
        /// </summary>
        private void UploadOfflineData()
        {
            if (!CheckServerSuccess())
                return;

            LoadLocalDbFile();

            List<_SqlClass> tc = new List<_SqlClass>();
            //List<_ClassClass> cc = new List<_ClassClass>(); 

            //如果最新的50条记录之前还有未交班的，肯定是员工操作问题了。这个在数据检查中发现问题吧。
            tc.Add(GetSqlClassData("PosShiftS", "select * from PosShiftS where EndDateTime not like '' order by Id desc limit 0,50"));
            Int64 idOld = GetPosOfflineDataIndex("ProductSIO");
            tc.Add(GetSqlClassData("ProductSIO", "select * from ProductSIO where Id>" + idOld));
            idOld = GetPosOfflineDataIndex("ProductSInZengSongS");
            tc.Add(GetSqlClassData("ProductSInZengSongS", "select * from ProductSInZengSongS where Id>" + idOld));
            idOld = GetPosOfflineDataIndex("VipIO");
            tc.Add(GetSqlClassData("VipIO", "select * from VipIO where Id>" + idOld));
            idOld = GetPosOfflineDataIndex("PosOrderS");
            tc.Add(GetSqlClassData("PosOrderS", "select * from PosOrderS where Id>" + idOld));

            DisposeLocalDbFile();
            Debug.Assert(FCNS.Data.SQLdata.SqlConfig != null);

            var vr = tc.Where(f => f.TableName != Table.PosOrderS.tableName);
            UploadSqlClassDataButPosOrder(vr);
            if (CheckUploadSqlClassData(vr))//如果相关记录上传不成功，就不上传 PosOrder 记录了，防止后台计算的时候数据不正确
            {
                vr = tc.Where(f => f.TableName == Table.PosOrderS.tableName);
                UploadPosOrderClassData(vr.FirstOrDefault());
                CheckUploadSqlClassData(vr);
            }

            DoubleH.Utility.Configuration.ConfigSerializer.SaveConfig(dataExchangeConfig, FCNS.Data.DbDefine.baseDir + "DataExchangeConfig.xml", false);
        }

        private _SqlClass GetSqlClassData(string tableName, string sql)
        {
#if SqlLog
            Table.ErrorS.WriteLogFile(sql);
#endif

            //if  not exists (select Id from UserS where Name like '001') select * from UserS else  select * from ProductS
            _SqlClass t = new _SqlClass();
            t.TableName = tableName;

            t.Table = GetLocalTable(sql);
            StringBuilder sbGuid = new StringBuilder();
            StringBuilder sbOrderNO = new StringBuilder();
            foreach (DataRow row in t.Table.Rows)
            {
                string guidString = row["GuidString"] as string;
                if (string.IsNullOrEmpty(guidString))
                    continue;

                sbGuid.Append("'" + guidString + "',");
                if (t.Table.Columns.Contains("OrderNO"))
                    sbOrderNO.Append("'" + row["OrderNO"] + "',");

                StringBuilder sbName = new StringBuilder("if  not exists (select Id from " + tableName + " where GuidString like '" + guidString + "')  insert into " + tableName + "(");
                StringBuilder sbValue = new StringBuilder(" values (");
                FormatRowToSqlString(t.Table.Columns, row, ref sbName, ref sbValue, false);
                t.UploadData.Add(sbName.ToString() + sbValue.ToString());
            }
            if (sbGuid.Length > 0)
                sbGuid.Remove(sbGuid.Length - 1, 1);
            if (sbOrderNO.Length > 0)
                sbOrderNO.Remove(sbOrderNO.Length - 1, 1);

            t.GuidStringList = sbGuid.ToString();
            t.OrderNOList = sbOrderNO.ToString();
            return t;
        }

        private void UploadSqlClassDataButPosOrder(System.Collections.IEnumerable tc)
        {
#if SqlLog
                foreach (string str in t.UploadData)
                    Table.ErrorS.WriteLogFile(str);
#endif
            //交班 
            foreach (_SqlClass t in tc)
                FCNS.Data.SQLdata.ExecuteNonQuery(t.UploadData.ToArray());
        }

        private bool CheckUploadSqlClassData(System.Collections.IEnumerable tc)
        {
            bool success = true;
            foreach (_SqlClass t in tc)
            {
                if (string.IsNullOrEmpty(t.GuidStringList))
                {
                    //AddLog(DateTime.Now + ":" + t.TableName + "->没有可上传数据");
                    continue;
                }

                //select * from AfterSaleServiceS where   cast(OrderNO as varchar)  in ('SH14020700001','SH14020700002')
               int count=(int)FCNS.Data.SQLdata.ExecuteScalar("select count(*) from " + t.TableName + " where  GuidString  in (" + t.GuidStringList + ")");
                if(count!=t.GuidStringList.Split(',').Length)
                {
                    success = false;
                    t.UploadSuccess = false;
                    AddLog(DateTime.Now + ":" + t.TableName + "->上传失败");
                }
                else
                {
                    t.UploadSuccess = true;
                    UpdatePosOfflineDataIndex(t.TableName, GetPosOfflineDataIndex(t.TableName) + (t.UploadData.Count));
                    AddLog(DateTime.Now + ":" + t.TableName + "->上传成功");
                }
            }
            return success;
        }

        private void UploadPosOrderClassData(_SqlClass c)
        {
            foreach (DataRow row in c.Table.Rows)
            {
                Table.PosOrderS ps = Table.PosOrderS.RowToClass(row, false);
                ps.InsertOffLine();
            }
        }

        //private void SetPropertyInfoValue(object obj, DataRow row,DataColumnCollection dcc)
        //{
        //    Type t = obj.GetType();
        //    System.Reflection.PropertyInfo pi = null;
        //    foreach (DataColumn dc in dcc)
        //    {
        //        pi = t.GetProperty(dc.ColumnName);
        //        if (pi == null)
        //            continue;

        //        pi.SetValue(obj, row[dc.ColumnName], null);
        //    }
        //}

        /// <summary>
        /// 转换成sql语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        private List<string> FormatTableToSql(string tableName, DataTable table)
        {
            List<string> result = new List<string>();
            foreach (DataRow row in table.Rows)
            {
                StringBuilder sbName = new StringBuilder("insert into " + tableName + "(");
                StringBuilder sbValue = new StringBuilder(" values (");
                FormatRowToSqlString(table.Columns, row, ref sbName, ref sbValue, true);
                result.Add(sbName.ToString() + sbValue.ToString());
            }

            return result;
        }

        private DataTable GetLocalTable(string sqlString)
        {
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlString, conSqlite);
            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
            }
            catch
            {
                AddLog(DateTime.Now + ":" + sqlString + "->异常");
            }
            finally { adapter.Dispose(); }
            return dt;
        }

        private void FormatRowToSqlString(DataColumnCollection dcc, DataRow row, ref StringBuilder sbName, ref StringBuilder sbValue, bool containsId)
        {
            foreach (DataColumn column in dcc)
            {
                if (column.ColumnName.StartsWith(FCNS.Data.DbDefine.ExtensionDataColumnPre) || (!containsId && column.ColumnName == "Id"))
                    continue;

                sbName.Append(column.ColumnName + ",");

                if (column.DataType == typeof(string))
                    sbValue.Append("'" + row[column] + "',");
                else
                    sbValue.Append(row[column] + ",");
            }

            sbName.Remove(sbName.Length - 1, 1);
            sbValue.Remove(sbValue.Length - 1, 1);
            sbName.Append(")");
            sbValue.Append(")");
        }

        private void AddLog(string text)
        {
            listBoxLog.Dispatcher.Invoke(new Action(() =>
            {
                listBoxLog.Items.Insert(0, text);
            }));

            string file = FCNS.Data.DbDefine.logDir + DateTime.Now.ToString("yyyyMMdd") + ".txt";
            Table.ErrorS.WriteLogFile(text);
        }

        private void SavePosOffLineMode()
        {
            cmdSqlite.CommandText = "select PosOfflineMode from SysConfig";
            object obj = cmdSqlite.ExecuteScalar();
            try
            {
                DoubleHConfig.AppConfig.PosOfflineMode = Convert.ToInt32(obj);
            }
            catch
            {
                DoubleHConfig.AppConfig.PosOfflineMode = 0;
            }
            ConfigSerializer.SaveConfig(DoubleHConfig.AppConfig, FCNS.Data.DbDefine.appConfigFile);
        }


        /// <summary>
        /// pos端离线数据上传完毕后，调用此方法更新标识并保存于配置文件中，防止重复上传。
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="id">已上传成功的最后Id值</param>
        private void UpdatePosOfflineDataIndex(string tableName, int id)
        {
            if (dataIndex.ContainsKey(tableName))
                dataIndex[tableName] = id;
            else
                dataIndex.Add(tableName, id);

            StringBuilder sb = new StringBuilder();
            foreach (KeyValuePair<string, int> kp in dataIndex)
                sb.Append(kp.Key + ":" + kp.Value + ";");

            dataExchangeConfig.PosOffLineDataIndex = sb.ToString();
            DoubleH.Utility.Configuration.ConfigSerializer.SaveConfig(dataExchangeConfig, FCNS.Data.DbDefine.baseDir + "DataExchangeConfig.xml", false);
        }
        /// <summary>
        /// 获取已上传成功的数据表其最后的Id值，
        /// 如果清空此值，则从0开始重新验证上传了。
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private int GetPosOfflineDataIndex(string tableName)
        {
            if (dataIndex.ContainsKey(tableName))
                return dataIndex[tableName];
            else
                return -1;
        }
    }
}